﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Microsoft.ApplicationBlocks.Data;

using YouHP.IDAL;
using YouHP.Model;

namespace YouHP.SqlServerDAL
{
    public class PrescriptionDAL : IPrescription
    {
        #region IPrescription Members

        public PrescriptionInfo GetPrescriptionInfo(int pid)
        {
            IDataReader reader = SqlHelper.ExecuteReader(
                Common.ConnStr,
                Common.PrescriptionSP_GetPrescriptionById, 
                pid) 
                as IDataReader;
            return CBO.FillObject<PrescriptionInfo>(reader);
        }

        public List<PrescriptionInfo> GetPrescriptionList(string condStr)
        {
            IDataReader reader = SqlHelper.ExecuteReader(
                Common.ConnStr,
                Common.PrescriptionSP_GetPrescription,
                condStr)
                as IDataReader;
            return CBO.FillCollection<PrescriptionInfo>(reader);
        }

        public int AddPrescription(PrescriptionInfo info)
        {
            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            SqlParameter[] outParams = new SqlParameter[] {
                new SqlParameter("@ParentId", SqlDbType.Int),
                new SqlParameter("@UserId", SqlDbType.Int),
                new SqlParameter("@DoctorId", SqlDbType.Int),
                new SqlParameter("@Name", SqlDbType.NVarChar, 50),
                new SqlParameter("@Score", SqlDbType.Float),
                new SqlParameter("@AverageScore", SqlDbType.Float),
                new SqlParameter("@DiseaseId", SqlDbType.Int),
                new SqlParameter("@DiseaseName", SqlDbType.NVarChar, 100),
                new SqlParameter("@IsClassic", SqlDbType.Int),
                new SqlParameter("@Description", SqlDbType.NVarChar, Int32.MaxValue),
                new SqlParameter("@Comment", SqlDbType.NVarChar, Int32.MaxValue),
                new SqlParameter("@CreateTime", SqlDbType.DateTime),
                new SqlParameter("@Id", SqlDbType.Int)
            };
            outParams[0].Value = info.ParentId;
            outParams[1].Value = info.UserId;
            outParams[2].Value = info.DoctorId;
            outParams[3].Value = info.Name;
            outParams[4].Value = info.Score;
            outParams[5].Value = info.AverageScore;
            outParams[6].Value = info.DiseaseId;
            outParams[7].Value = info.DiseaseName;
            outParams[8].Value = info.IsClassic;
            outParams[9].Value = info.Description;
            outParams[10].Value = info.Comment;
            outParams[11].Value = info.CreateTime;
            outParams[12].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQuery(ts, CommandType.StoredProcedure, Common.PrescriptionSP_AddPrescription, outParams);
            ts.Commit();
            conn.Close();

            return Convert.ToInt32(outParams[12].Value.ToString());
        }

        public bool UpdatePrescription(PrescriptionInfo info)
        {
            int count = SqlHelper.ExecuteNonQuery(
                 Common.ConnStr,
                 Common.PrescriptionSP_UpdatePrescription,
                 info.Id,
                 info.ParentId,
                 info.UserId,
                 info.DoctorId,
                 info.Name,
                 info.Score,
                 info.AverageScore,
                 info.DiseaseId,
                 info.DiseaseName,
                 info.IsClassic,
                 info.Description,
                 info.Comment,
                 info.CreateTime);
            return count > 0;
        }

        public bool DeletePrescription(int pid)
        {
            int count = SqlHelper.ExecuteNonQuery(
                Common.ConnStr,
                Common.PrescriptionSP_DeletePrescriptionById,
                pid);
            return count > 0;
        }

        public List<PrescriptionInfo> GetPrescriptionList(int pageSize, int pageIndex, string condStr, out int count)
        {
            string orderStr = "[Id] asc";
            return GetPrescriptionList(pageSize, pageIndex, condStr, orderStr, out count);
        }

        public List<PrescriptionInfo> GetPrescriptionList(int pageSize, int pageIndex, string condStr, string orderStr, out int count)
        {
            DataSet ds = SqlHelper.ExecuteDataset(
                Common.ConnStr,
                Common.PrescriptionSP_GetPrescriptionList,
                pageSize, 
                pageIndex, 
                condStr, 
                orderStr);

            IDataReader dr = ds.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }

            dr = ds.Tables[1].CreateDataReader();
            return CBO.FillCollection<PrescriptionInfo>(dr);
        }

        #endregion

        public List<DiseasePrescriptionStat> GetDiseasePrescriptionStat(int count)
        {
            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PrescriptionSP_GetPrescriptionsCountOfDisease,
                count);

            return CBO.FillCollection<DiseasePrescriptionStat>(dr);
        }
    }
}
